-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		duylv00360
-- Create date: 4/5/2011
-- Description:	This stored procedure use to search order
-- =============================================
ALTER PROCEDURE SearchOrder 
	-- Add the parameters for the stored procedure here
	@isDelete int = 2,		--isDelete = 1 search deleted record, isDelete = 0 search non deleted record and other for all
	@isMaterial int = 2,	--isMaterial = 1 search order of material, isMaterial = 0 search order of product and other for all
	@isImport int = 2,		--isImport = 1 search imported order, isImport = 0 search exportec order and other for all
	@orderCode varchar(16) = null,
	@employeeAID int = 0,
	@fromDate datetime = null,
	@toDate datetime = null,
	@isAllEmployee int = -1, -- we define if isAllEmployee = -1 , we will search in all employees
	@isOrderCodeAll bit = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @fromDateSearch datetime = '1900-01-01 00:00:00.000'
    DECLARE @toDateSearch datetime = '3000-01-01 00:00:00.000'
   
    IF @fromDate IS NULL
		BEGIN
			SET @fromDateSearch = '1900-01-01 00:00:00.000'
			PRINT 'fromDate is null then it is set to defaul value'
		END
	ELSE
		BEGIN
			SET @fromDateSearch = @fromDate
		END
	IF @toDate IS NULL
		BEGIN
			SET @toDateSearch = '3000-01-01 00:00:00.000'
			PRINT 'toDate is null then it is set to defaul value'
		END
	ELSE 
		BEGIN
			SET @toDateSearch = @toDate
		END
	 
		BEGIN
		IF @isMaterial = 1
			BEGIN
				IF @isDelete = 1
					BEGIN
						IF @isImport = 1
							BEGIN
								SELECT	[Order].OrderID,
										[Order].OrderCode,
										[Order].Date as OrderDate,
										[Order].EmployeeAID, 
										E.FullName EmployeeAName,
										[Order].ContactID, 
										c.FullName ContactName,
										[Order].EmployeeBID,
										em.FullName EmployeeBName, 
										[Order].IsImport,
										[Order].IsDelete
								FROM	[Order]
								LEFT JOIN	Employee E
								ON		[Order].EmployeeAID = E.EmployeeID 
								LEFT JOIN	Employee em
								ON		[Order].EmployeeBID = em.EmployeeID 
								LEFT JOIN	Contact c
								ON		[Order].ContactID = c.ContactID	
								WHERE	[Order].OrderCode LIKE '%M%' 
										AND
										[Order].IsDelete = 1
										AND
										[Order].IsImport = 1
										AND
										([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
										AND
										([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
										AND
										([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
								ORDER BY [Order].Date DESC
									
							END
						ELSE 
							BEGIN
								IF @isImport = 0
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%M%' 
												AND
												[Order].IsDelete = 1
												AND
												[Order].IsImport = 0
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%M%' 
												AND
												[Order].IsDelete = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
										
									END
							END
					END
				ELSE
					BEGIN
						IF @isDelete = 0
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%M%' 
												AND
												[Order].IsDelete = 0
												AND
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%M%' 
														AND
														[Order].IsDelete = 0
														AND
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%M%' 
														AND
														[Order].IsDelete = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
												 
											END
									END
							END
						ELSE
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%M%' 
												AND
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%M%' 
														AND
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%M%' 
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
														
												 
											END
									END							
							END
		
					END
			END					
		ELSE
			BEGIN
				IF @isMaterial = 0
					BEGIN
					IF @isDelete = 1
					BEGIN
						IF @isImport = 1
							BEGIN
								SELECT	[Order].OrderID,
										[Order].OrderCode,
										[Order].Date as OrderDate,
										[Order].EmployeeAID, 
										E.FullName EmployeeAName,
										[Order].ContactID, 
										c.FullName ContactName,
										[Order].EmployeeBID,
										em.FullName EmployeeBName, 
										[Order].IsImport,
										[Order].IsDelete
								FROM	[Order]
								LEFT JOIN	Employee E
								ON		[Order].EmployeeAID = E.EmployeeID 
								LEFT JOIN	Employee em
								ON		[Order].EmployeeBID = em.EmployeeID 
								LEFT JOIN	Contact c
								ON		[Order].ContactID = c.ContactID	
								WHERE	[Order].OrderCode LIKE '%P%' 
										AND
										[Order].IsDelete = 1
										AND
										[Order].IsImport = 1
										AND
										([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
										AND
										([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
										AND
										([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
								ORDER BY [Order].Date DESC
							END
						ELSE 
							BEGIN
								IF @isImport = 0
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%P%' 
												AND
												[Order].IsDelete = 1
												AND
												[Order].IsImport = 0
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%P%' 
												AND
												[Order].IsDelete = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
										
									END
							END
					END
				ELSE
					BEGIN
						IF @isDelete = 0
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%P%' 
												AND
												[Order].IsDelete = 0
												AND
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%P%' 
														AND
														[Order].IsDelete = 0
														AND
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%P%' 
														AND
														[Order].IsDelete = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
									END
							END
						ELSE
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	[Order].OrderCode LIKE '%P%' 
												AND
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%P%' 
														AND
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	[Order].OrderCode LIKE '%P%' 
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 ) 
												ORDER BY [Order].Date DESC
											END
									END							
							END
		
					END
			END	 
				ELSE
					BEGIN
						BEGIN
					IF @isDelete = 1
					BEGIN
						IF @isImport = 1
							BEGIN
								SELECT	[Order].OrderID,
										[Order].OrderCode,
										[Order].Date as OrderDate,
										[Order].EmployeeAID, 
										E.FullName EmployeeAName,
										[Order].ContactID, 
										c.FullName ContactName,
										[Order].EmployeeBID,
										em.FullName EmployeeBName, 
										[Order].IsImport,
										[Order].IsDelete
								FROM	[Order]
								LEFT JOIN	Employee E
								ON		[Order].EmployeeAID = E.EmployeeID 
								LEFT JOIN	Employee em
								ON		[Order].EmployeeBID = em.EmployeeID 
								LEFT JOIN	Contact c
								ON		[Order].ContactID = c.ContactID	
								WHERE	
										[Order].IsDelete = 1
										AND
										[Order].IsImport = 1
										AND
										([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
										AND
										([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
										AND
										([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
								ORDER BY [Order].Date DESC
							END
						ELSE 
							BEGIN
								IF @isImport = 0
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	
												[Order].IsDelete = 1
												AND
												[Order].IsImport = 0
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	
												[Order].IsDelete = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
							END
					END
				ELSE
					BEGIN
						IF @isDelete = 0
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	
												[Order].IsDelete = 0
												AND
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	
														[Order].IsDelete = 0
														AND
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	
														[Order].IsDelete = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
										END
									END
							END
						ELSE
							BEGIN
								IF @isImport = 1
									BEGIN
										SELECT	[Order].OrderID,
												[Order].OrderCode,
												[Order].Date as OrderDate,
												[Order].EmployeeAID, 
												E.FullName EmployeeAName,
												[Order].ContactID, 
												c.FullName ContactName,
												[Order].EmployeeBID,
												em.FullName EmployeeBName, 
												[Order].IsImport,
												[Order].IsDelete
										FROM	[Order]
										LEFT JOIN	Employee E
										ON		[Order].EmployeeAID = E.EmployeeID 
										LEFT JOIN	Employee em
										ON		[Order].EmployeeBID = em.EmployeeID 
										LEFT JOIN	Contact c
										ON		[Order].ContactID = c.ContactID	
										WHERE	
												[Order].IsImport = 1
												AND
												([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
												AND
												([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
												AND
												([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
										ORDER BY [Order].Date DESC
									END
								ELSE 
									BEGIN
										IF @isImport = 0
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	
														[Order].IsImport = 0
														AND
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
											END
										ELSE
											BEGIN
												SELECT	[Order].OrderID,
														[Order].OrderCode,
														[Order].Date as OrderDate,
														[Order].EmployeeAID, 
														E.FullName EmployeeAName,
														[Order].ContactID, 
														c.FullName ContactName,
														[Order].EmployeeBID,
														em.FullName EmployeeBName, 
														[Order].IsImport,
														[Order].IsDelete
												FROM	[Order]
												LEFT JOIN	Employee E
												ON		[Order].EmployeeAID = E.EmployeeID 
												LEFT JOIN	Employee em
												ON		[Order].EmployeeBID = em.EmployeeID 
												LEFT JOIN	Contact c
												ON		[Order].ContactID = c.ContactID	
												WHERE	
														([Order].Date <= @toDateSearch and [Order].Date > @fromDateSearch) 														
														AND
														([Order].OrderCode LIKE '%' + @orderCode + '%' OR @isOrderCodeAll = 1)
														AND
														([Order].EmployeeAID = @employeeAID OR @isAllEmployee = -1 )
												ORDER BY [Order].Date DESC
														
																 
											END
									END							
							END
		
					END
			END	 
					END
			END
					
		END
			 
END
GO
/*
use WarehouseManagement
go
exec SearchOrder @ordercode = 'p',@employeeAid= 2
*/